﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CamDo.DTO;
using System.Data.SqlClient;
using System.Data;

namespace CamDo.DAO
{
    class CamDoDAO
    {
        private Connect connectDTO;

        internal Connect ConnectDTO
        {
            get { return connectDTO; }
            set { connectDTO = value; }
        }
        private HangHoa matHangDTO;

        internal HangHoa MatHangDTO
        {
            get { return matHangDTO; }
            set { matHangDTO = value; }
        }
        private GiaoDich giaoDichDTO;

        internal GiaoDich GiaoDichDTO
        {
            get { return giaoDichDTO; }
            set { giaoDichDTO = value; }
        }
        SqlConnection connect;

        public SqlConnection Connect
        {
            get { return connect; }
            set { connect = value; }
        }
        private String error = "";

        public String Error
        {
            get { return error; }
            set { error = value; }
        }
        public CamDoDAO()
        {
            matHangDTO  = new HangHoa();
            giaoDichDTO = new GiaoDich();
            connectDTO  = new Connect();
            connect = connectDTO.GetConnect();

        }
        public DataTable getAllData()
        {
            DataTable d = new DataTable();
            try
            {
                String sql = "SELECT HANG_CAM_DO.MaMH as 'Mã mặt hàng', HANG_CAM_DO.TenMH as 'Tên mặt hàng',DANH_MUC.TenDM as 'Loại Mặt Hàng',HANG_CAM_DO.SoLuong as 'Số lượng',HANG_CAM_DO.NhaSX as 'Nhà sản xuất',HANG_CAM_DO.GiaTriGoc as 'Giá trị gốc',HANG_CAM_DO.NgayCam as 'Thời điểm cầm đồ', HANG_CAM_DO.ThoiGianCam as 'Thời gian cầm', HANG_CAM_DO.LaiSuat as 'Lãi suất', HANG_CAM_DO.TienChuoc as 'Số tiền chuộc', HANG_CAM_DO.GiayToKemTheo as 'Giấy tờ kèm theo', HANG_CAM_DO.TenKhachHang as 'Tên khách hàng',HANG_CAM_DO.SoDT as 'Số điện thoại', HANG_CAM_DO.SoCMND as 'Số CMND',TRANG_THAI.TenTrangThai as 'Tình trạng mặt hàng'  FROM HANG_CAM_DO,DANH_MUC,TRANG_THAI WHERE HANG_CAM_DO.LoaiMH = DANH_MUC.MaDM AND HANG_CAM_DO.TrangThai = TRANG_THAI.MaTrangThai";
                //String sql = "SELECT *  FROM HANG_CAM_DO";
                SqlDataAdapter da = new SqlDataAdapter(sql, this.connect);
                da.Fill(d);
           }
            catch (Exception ex)
            {
                this.error = ex.ToString();
            }
            return d;
        }
        public bool saveARow()
        {
            int result = 0;
            try
            {
                this.connect.Open();
                String strsql = "insert into HANG_CAM_DO(MaMH,TenMH,SoLuong,LoaiMH,NhaSX,GiaTriGoc,NgayCam,ThoiGianCam,LaiSuat,TienChuoc,GiayToKemTheo,TenKhachHang,SoDT,SoCMND) values(";
                strsql += "'" + matHangDTO.MaMatHang + "','" + matHangDTO.TenMatHang + "','" + matHangDTO.SoLuong + "','" + matHangDTO.LoadMatHang + "','" + matHangDTO.NhaSanXuat + "','" + matHangDTO.GiaTriGoc;
                strsql += "','" + matHangDTO.NgayCamDo + "','" + matHangDTO.ThoigianCam + "','" + matHangDTO.LaiSuat + "','" + matHangDTO.TienChuoc + "','" + matHangDTO.GiayToKem + "','" + matHangDTO.TenKhachHang + "','" + matHangDTO.SoDienThoai + "','" + matHangDTO.SoCMND + "')";

                // String strsql = "insert into THONGTINME(MA_BN,TUANTHAI) values('100001','10')";

                SqlCommand cmd = new SqlCommand(strsql, this.connect);
                result = cmd.ExecuteNonQuery();
                this.connect.Close();
            }
            catch (Exception ex)
            {
                Error = "Error save DATA :" + ex.Message;
            }

            if (result <= 0)
                return false;
            return true;
        }
        public HangHoa getARow(String maMatHang)
        {
            HangHoa matHang = new HangHoa();
            DataTable d = new DataTable();
            try
            {
                this.connect.Open();
                string sql = "select * from HANG_CAM_DO where HANG_CAM_DO.MaMH='" + maMatHang + "'";
                SqlDataAdapter da = new SqlDataAdapter(sql, this.connect);
                da.Fill(d);
                if (d.Rows.Count >0)
                {
                    matHang.MaMatHang = (string)d.Rows[0][0];
                    matHang.TenMatHang = (string)d.Rows[0][1];
                    matHang.SoLuong = (int)d.Rows[0][2];
                    matHang.LoadMatHang = (int)d.Rows[0][3];
                    matHang.NhaSanXuat = (string)d.Rows[0][4];
                    matHang.GiaTriGoc = (int)d.Rows[0][5];
                    
                    
                    matHang.ThoigianCam = (string)d.Rows[0][8];
                    matHang.LaiSuat = (int)d.Rows[0][10];
                    
                    matHang.TienChuoc = (int)d.Rows[0][11];
                    matHang.GiayToKem = (string)d.Rows[0][12];
                    matHang.TenKhachHang = (string)d.Rows[0][13];
                    matHang.SoDienThoai = (string)d.Rows[0][14];
                    matHang.SoCMND = (string)d.Rows[0][15];
                    
                    //matHang.NgayCamDo = (string)d.Rows[0][0];

                    //error = (String)d.Rows[0][13];
                    
                }


                
                this.connect.Close();
            }
            catch (Exception ex)
            {
                error = "Loi " + ex.ToString();
                
            }
            return matHang;
        }
        public void deleteARow(String maMatHang)
        {
            try
            {
                this.connect.Open();
                using (SqlCommand command = new SqlCommand("DELETE FROM HANG_CAM_DO  WHERE HANG_CAM_DO.MaMH='" + maMatHang + "' " , this.connect))
                {
                    command.ExecuteNonQuery();
                }
                this.connect.Close();
            }
            catch( Exception ex)
            {
                this.error = ex.ToString();
            }
        }



    }
}
